/**
 * 项目名：五位一体化工安全信息平台
 * 日期：    2019-11-24 22:18:20
 * Copyright (c) 2015- joysuch-版权所有
 */

package com.joysuch.wwyt.risk.repository;

import com.joysuch.wwyt.risk.bean.RiskPointBeanVo;
import com.joysuch.wwyt.risk.bean.RiskPointEvaluationHistoryId;
import com.joysuch.wwyt.risk.entity.RiskDistrict;
import com.joysuch.wwyt.risk.entity.RiskPoint;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.geo.Point;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.querydsl.QuerydslPredicateExecutor;
import org.springframework.data.repository.query.Param;

import java.util.Collection;
import java.util.Date;
import java.util.List;
import java.util.Set;

/**
 * RiskPointDao
 */
public interface RiskPointDao extends JpaRepository<RiskPoint, Long>, QuerydslPredicateExecutor<RiskPoint> {
    public Page<RiskPoint> findAll(Specification<RiskPoint> spec, Pageable pageable);

    public Page<RiskPoint> findByState(Integer state, Pageable pageable);

    public Page<RiskPoint> findByRiskDistrict(RiskDistrict riskDistrict, Pageable pageable);

    public RiskPoint findFirstByRiskDistrictAndName(RiskDistrict riskDistrict, String name);

    public RiskPoint save(RiskPoint bean);

    public void delete(RiskPoint bean);

    @Query("select count(id) from RiskPoint where riskDistrict.id = ?1 and (shared is null or shared =0) AND DELETE_FLAG='0' AND status =3")
    public Long pointCountInRiskDistrict(Long districtId);

    public RiskPoint findFirstByNameAndSharedIsNull(String name);

    public RiskPoint findFirstByNameAndShared(String name, Integer shared);

    @Query("select name from RiskPoint where id = ?1")
    public String findNameById(Long id);

    @Query("select count(m) from RiskPoint m where name = ?1")
    long countOfName(String name);

    @Query("select count(m) from RiskPoint m where name = ?1 and id != ?2")
    long countOfNameExcludeId(String name, Long id);

    @Query(value = "select id,'jha' as type, create_time as createTime from wwyt_risk_point_evaluation_jha  where risk_point_id = ?1 "
            + " union all select id,'scl' as type, create_time  as createTime  from wwyt_risk_point_evaluation_scl where risk_point_id = ?1 order by createTime desc", countQuery = "select count(*) from (select id from wwyt_risk_point_evaluation_jha where risk_point_id=?1 union all select id from wwyt_risk_point_evaluation_scl where risk_point_id=?1) re", nativeQuery = true)
    public Page<RiskPointEvaluationHistoryId> findEvaluationHistoryById(Long riskPointId, Pageable page);

    //public RiskPoint findById (String id);

    public RiskPoint getById(Long id);

    int countByCreateTimeAfter(Date time);

    @Query(value = "SELECT p.RISK_DISTRICT_ID FROM wwyt_risk_point p WHERE p.id =?1", nativeQuery = true)
    Long getRiskDistrictIdById(Long id);

    @Query(value = "SELECT p.* FROM wwyt_risk_point p WHERE p.`STATE` =0 AND (p.shared IS NULL OR p.shared =0) AND p.DELETE_FLAG='0' AND status =3", nativeQuery = true)
    List<RiskPoint> getTwoFormList();

    @Query("select r from RiskPoint r where r.state=0 and (r.shared is null or r.shared=0)")
    List<RiskPoint> findAllPoint();

    List<RiskPoint> findByRiskDistrictId(Long id);

    @Query(value = "SELECT * FROM wwyt_risk_point WHERE RISK_DISTRICT_ID = ?1 AND (shared IS NULL OR shared =0) AND DELETE_FLAG='0' AND status =3", nativeQuery = true)
    Page<RiskPoint> getPointInDistrictListByPage(Long riskDistrictId, Pageable pageable);

    @Query(value = "SELECT * FROM wwyt_risk_point WHERE RISK_DISTRICT_ID = ?1 AND (shared IS NULL OR shared =0) AND DELETE_FLAG='0' AND status =3", nativeQuery = true)
    List<RiskPoint> getPointInDistrictList(Long riskDistrictId);

    @Query(value = "SELECT min(RISK_LEVEL) FROM wwyt_risk_point WHERE RISK_DISTRICT_ID = ?1 AND (shared IS NULL OR shared =0) AND DELETE_FLAG='0'AND status =3", nativeQuery = true)
    Integer getMinPointLevel(Long id);

    @Query(value = "SELECT * FROM wwyt_risk_point WHERE control_risk_level IS NOT NULL AND (shared IS NULL OR shared =0) AND DELETE_FLAG='0'AND status =3" +
            "AND if( :name != '' , NAME LIKE %:name%,1=1 )" +
            "AND if (:level !='',RISK_LEVEL = :level,1=1 )", nativeQuery = true)
    List<RiskPoint> getControlRiskPointList(@Param("name") String name, @Param("level") Integer level);

    List<RiskPoint> findBySubType(String subType);

    int countByType(String code);

    int countBySubType(String code);

    @Query(value = "SELECT min(control_risk_level) FROM wwyt_risk_point WHERE RISK_DISTRICT_ID = ?1 AND (shared IS NULL OR shared =0) AND DELETE_FLAG='0' AND status =3", nativeQuery = true)
    Integer getMinControlLevel(Long id);

    @Query(value = "SELECT rp.* FROM wwyt_risk_point rp WHERE rp.id IN (SELECT DISTINCT risk_point_id  FROM `wwyt_risk_point_business_evaluation_detail` WHERE method_analyze_type = 2 AND content IS NOT NULL AND delete_flag = '0' " +
            "AND if(:controlLevelId != '',control_level_id = :controlLevelId,1=1) " +
            "AND if(:controlState != '',control_state = :controlState ,1=1) " +
            "AND if(:responseDepartId != '',response_depart_id = :responseDepartId ,1=1)) " +
            "AND rp.control_risk_level IS NOT NULL AND (rp.shared IS NULL OR rp.shared =0) AND rp.DELETE_FLAG='0' AND rp.STATUS =3", nativeQuery = true)
    List<RiskPoint> getControlPointList(@Param("controlLevelId") Long controlLevelId,
                                        @Param("controlState") Integer controlState,
                                        @Param("responseDepartId") Long responseDepartId);

    @Query(value = "SELECT min(RISK_LEVEL) FROM wwyt_risk_point WHERE ID in :idList AND shared =1 AND DELETE_FLAG='0'", nativeQuery = true)
    Integer getLibraryMinPointLevel(List<Long> idList);

    @Query(value = "SELECT * FROM wwyt_risk_point WHERE code = ?1 AND (shared IS NULL OR shared =0) AND DELETE_FLAG='0' ", nativeQuery = true)
    List<RiskPoint> findByCode(String code);

    List<RiskPoint> findByCodeIn(Set<String> codeList);

    @Query(value = "SELECT p.* FROM wwyt_risk_point p , wwyt_risk_district d WHERE p.LAST_EVALUATION_TIME IS NOT NULL AND (p.shared IS NULL OR p.shared =0) " +
            " AND p.DELETE_FLAG ='0' AND p.risk_district_id = d.id and d.delete_flag='0' and (d.state is null or d.state !=44)", nativeQuery = true)
    List<RiskPoint> findByTimeIsNotNull();

    @Query(value = "SELECT p.* FROM wwyt_risk_point p INNER JOIN wwyt_risk_district d ON p.RISK_DISTRICT_ID = d.id WHERE d.responsible_dept in ?1 AND p.LAST_EVALUATION_TIME IS NOT NULL AND (p.shared IS NULL OR p.shared =0) AND p.DELETE_FLAG ='0'  AND (d.state is null or d.state !=44)", nativeQuery = true)
    List<RiskPoint> findByDistrictDeptAndTimeIsNotNull(List<Long> ids);

    @Query(value = "SELECT p.* FROM wwyt_risk_point p INNER JOIN wwyt_risk_district d ON p.RISK_DISTRICT_ID = d.id WHERE d.responsible_dept in ?1 AND p.RISK_DISTRICT_ID in ?2 AND p.LAST_EVALUATION_TIME IS NOT NULL AND (p.shared IS NULL OR p.shared =0) AND p.DELETE_FLAG ='0'  AND (d.state is null or d.state !=44)", nativeQuery = true)
    List<RiskPoint> findByDistrictAndTimeIsNotNull(List<Long> departIds,List<Long> districtIdsList);

    @Query(value = "SELECT * FROM wwyt_risk_point WHERE LAST_EVALUATION_TIME IS NOT NULL AND shared =1 AND DELETE_FLAG='0'", nativeQuery = true)
    List<RiskPoint> findByTimeIsNotNullAndShared();

    @Query(value = "SELECT * FROM wwyt_risk_point WHERE status =?1 AND (shared IS NULL OR shared =0) AND DELETE_FLAG='0' ", nativeQuery = true)
    List<RiskPoint> findAllByShared(int i);

    @Query(value = "SELECT * FROM wwyt_risk_point WHERE code = ?1 AND shared = ?2 AND DELETE_FLAG='0' ", nativeQuery = true)
    List<RiskPoint> findByCodeAndShared(String code, int i);

    @Query(value = "SELECT min(stable_risk_level) FROM wwyt_risk_point WHERE RISK_DISTRICT_ID = ?1 AND (shared IS NULL OR shared =0) AND DELETE_FLAG='0' AND status =3 AND (show_state IS NULL OR show_state !=44)", nativeQuery = true)
    Integer getMinPointStableLevel(Long riskDistrictId);

    @Query(value = "SELECT min(real_time_risk_level) FROM wwyt_risk_point WHERE RISK_DISTRICT_ID = ?1 AND (shared IS NULL OR shared =0) AND DELETE_FLAG='0' AND status =3 AND (show_state IS NULL OR show_state !=44)", nativeQuery = true)
    Integer getMinRealRiskLevel(Long riskDistrictId);

    @Query(value = "SELECT * FROM wwyt_risk_point WHERE name =?1 AND risk_district_id =?2 AND (shared IS NULL OR shared =0) AND DELETE_FLAG='0'AND (show_state IS NULL OR show_state !=44) limit 1", nativeQuery = true)
    RiskPoint getOnebyNameAndDistrictId(String name, Long id);

    @Query(value = "SELECT * FROM wwyt_risk_point WHERE name =?1 AND risk_district_id =?2 AND id!=?3 AND (shared IS NULL OR shared =0) AND DELETE_FLAG='0'AND (show_state IS NULL OR show_state !=44) limit 1", nativeQuery = true)
    RiskPoint getOnebyNameAndDistrictIdAndId(String name, Long riskDistrictId, Long riskPointId);

    @Query(value = "SELECT * FROM wwyt_risk_point WHERE  (shared IS NULL OR shared =0) AND DELETE_FLAG='0'AND (show_state IS NULL OR show_state !=44)", nativeQuery = true)
    List<RiskPoint> getAllRiskPoints();

    @Query(value = "SELECT * FROM wwyt_risk_point WHERE RISK_DISTRICT_ID =?1 AND (shared IS NULL OR shared =0) AND DELETE_FLAG='0'AND (show_state IS NULL OR show_state !=44) AND status =3 ", nativeQuery = true)
    List<RiskPoint> findAllAuditedPointsByDistrictId(Long districtId);

    @Query(value = "SELECT * FROM wwyt_risk_point WHERE name =?1 AND shared =1 AND DELETE_FLAG='0'AND (show_state IS NULL OR show_state !=44) limit 1", nativeQuery = true)
    RiskPoint getOnebyNameAndShared(String name);

    @Query(value = "SELECT * FROM wwyt_risk_point WHERE name =?1 AND id!=?2 AND shared =1 AND DELETE_FLAG='0'AND (show_state IS NULL OR show_state !=44) limit 1", nativeQuery = true)
    RiskPoint getOnebyNameAndId(String name, Long id);

    @Query(value = "SELECT f.* FROM wwyt_risk_point f WHERE (f.show_state IS NULL OR f.show_state !=44) AND (f.LOCATION is not null or f.POINTS is not null) AND f.DELETE_FLAG = '0' AND f.status = 3", nativeQuery = true)
    List<RiskPoint> findAllHaveLocation();

    @Query(value = "select * from wwyt_risk_point where id = ?1", nativeQuery = true)
    RiskPoint findByIdIgnoreDeleteStatus(Long id);

    @Query(value = "select * from wwyt_risk_point where id in ?1", nativeQuery = true)
    List<RiskPoint> findByIdInIgnoreDeleteStatus(Collection<Long> idSet);

    @Query(value = "SELECT * FROM wwyt_risk_point WHERE status =?1 AND org_code like ?2% AND tenent_id =?3 AND (shared IS NULL OR shared =0) AND DELETE_FLAG='0' ", nativeQuery = true)
    List<RiskPoint> findAllBySharedAndOrgCodeAndTenentId(int i, String orgCode, Long tenentId);

    @Query(value = "SELECT * FROM wwyt_risk_point WHERE DELETE_FLAG='0'AND (shared IS NULL OR shared =0) AND id IN ?1", nativeQuery = true)
    List<RiskPoint> findByIds(Set<Long> keySet);

    @Query(value = "SELECT * FROM wwyt_risk_point WHERE sign_in_method = 1 AND nfc =?1 AND DELETE_FLAG='0' AND (show_state IS NULL OR show_state !=44)", nativeQuery = true)
    List<RiskPoint> findByNfc(String nfc);

    @Query(value = "SELECT * FROM wwyt_risk_point WHERE id IN ?1 AND DELETE_FLAG='0' AND (show_state IS NULL OR show_state !=44) ", nativeQuery = true)
    List<RiskPoint> findByIdsNotDeleted(Set<Long> keySet);

    @Query(value = "SELECT * FROM wwyt_risk_point WHERE DELETE_FLAG='0' AND (show_state IS NULL OR show_state !=44) ", nativeQuery = true)
    List<RiskPoint> findList();


    @Query(value = "SELECT * FROM wwyt_risk_point WHERE DELETE_FLAG='0'AND (shared IS NULL OR shared =0) AND id IN ?1", nativeQuery = true)
    List<RiskPoint> findByIds(List<Long> ids);
}
